﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TSQLDocumentorViewer.Models;

namespace TSQLDocumentorViewer.Controllers
{
    public class HomeController : Controller
    {
        string DocumentaionGuide = "\n<hr class='ObjectHeader'>\n# Guide to Documenting your code\n<hr class='ObjectFooter'>" + @"
        ## How to create comments
        - - -
        To create new Comments just create a commented block of code as below<br>
        `/* <YourHeading>Your comment goes here</YourHeading> */` <br>  
        You can use any alpha numeric characters for your tags excluding spaces and punctuation. Substitue spaces with 
        an '_' underscore; they will be replaced with spaces. Also you can use the word '`code`' for tags and anything 
        between the tags will be returned as preformatted code.
            
        ## Formatting
        - - -
        # H1 Headers is for Object Names Only
        ## H2 Headers requires two hashes (##)

        * Example `/* <##YourHeading>Your comment goes here</##YourHeading> */`

        ### H3 Headers requires one hashes (#)

        * Example `/* <#YourHeading>Your comment goes here</#YourHeading> */`

        #### H4 Headers requires no hashes 
            
        * Example `/* <YourHeading>Your comment goes here</YourHeading> */`
        * To get lists use asterix followed by a space and then your text. Example '<code>* Your comment goes here</code>'. It also needs a blank line above it
        * To get a horizontal line simple type '<code>- - -</code>' on its own line
                        
        #### <a href='http://daringfireball.net/projects/markdown/syntax' target='_blank'>More about Markdown syntax here<a>
        <br />
        ";

        [HttpPost]
        public ActionResult Index(DocumentationViewModel dvm)
        {

            string Documentaion = "";
            string DocumentaionIndex = "<hr class='ObjectHeader'>\n# Table of Contents #\n<hr class='ObjectFooter'>\n";

            List<sysObjects> SysObjects = new List<sysObjects>();

            string ConnectionString = dvm.ConnectionString;
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                //Remove Default LocalSqlServer SQLEXPRESS Connection String
                var ConnectionStrings = System.Configuration.ConfigurationManager.ConnectionStrings
                    .Cast<System.Configuration.ConnectionStringSettings>()
                    .Where(cn => cn.Name != "LocalSqlServer")
                    .Select(cn => cn.ConnectionString)
                    .ToList();

                if (ConnectionStrings.Count > 0)
                    ConnectionString = ConnectionStrings[0];
            }

            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                string SysObject_SQLCommand = @"
                SELECT
	                [objects].[name] ObjectName
                   ,SCHEMA_NAME([objects].[schema_id]) SchemaName
                   ,[objects].[type_desc] TypeName
                FROM
	                [sys].[objects]
                WHERE
	                [objects].[type] IN ('FN', 'IF', 'P', 'TR', 'TF', 'U', 'V')
                ORDER BY
	                [objects].[schema_id]
                   ,[objects].[type]
                   ,[objects].[name]
                ";

                using (SqlCommand cm = new SqlCommand(SysObject_SQLCommand, cn))
                {
                    cm.CommandType = System.Data.CommandType.Text;
                    cn.Open();
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            SysObjects.Add(new sysObjects()
                            {
                                ObjectName = dr["ObjectName"].ToString(),
                                SchemaName = dr["SchemaName"].ToString(),
                                TypeName = dr["TypeName"].ToString()
                            }
                            );
                        }
                    }
                    cn.Close();
                }

                string SQLCommand = @"
                SELECT
	                SCHEMA_NAME([o].[schema_id]) SCHEMA_NAME
                   ,[o].[type_desc]
                   ,[o].[name] AS OBJECT_NAME
                   ,[m].[definition]
                FROM
	                [sys].[sql_modules] m
	                INNER JOIN [sys].[objects] o
	                ON [m].[object_id] = [o].[object_id]
                UNION ALL
                SELECT
	                SCHEMA_NAME([o].[schema_id]) SCHEMA_NAME
                   ,[o].[type_desc] + ' (' + [p].[class_desc] COLLATE Latin1_General_CI_AS + ') '
                   ,[o].[name] COLLATE Latin1_General_CI_AS + ' ' + ISNULL(CASE	WHEN [p].[class] = 1 THEN [columns].[name]
																                WHEN [p].[class] = 2 THEN [parameters].[name]
																                WHEN [p].[class] = 7 THEN [indexes].[name]
																                ELSE ''
														                   END, '') COLLATE Latin1_General_CI_AS
                   ,CAST([p].[value] AS NVARCHAR(MAX)) [definition]
                FROM
	                sys.extended_properties p
	                LEFT JOIN sys.[objects] o
	                ON p.major_id = o.object_id
	                LEFT JOIN [sys].[parameters]
	                ON [p].[minor_id] = [parameters].[parameter_id]
	                   AND [p].[major_id] = [parameters].[object_id]
	                LEFT JOIN [sys].[columns]
	                ON [p].[minor_id] = [columns].[column_id]
	                   AND [p].[major_id] = [columns].[object_id]
	                LEFT JOIN [sys].[indexes]
	                ON [p].[minor_id] = [indexes].[index_id]
	                   AND [p].[major_id] = [indexes].[object_id]
                WHERE
	                class IN (1, 2, 7)
                ORDER BY
	                1
                   ,2
                   ,3
                ";

                using (SqlCommand cm = new SqlCommand(SQLCommand, cn))
                {
                    cm.CommandType = System.Data.CommandType.Text;
                    cn.Open();
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            string ObjectDefinition = dr["definition"].ToString();
                            string ReferencedObjects = "";
                            List<sysObjects> sysObjectsFound = new List<sysObjects>();
                            string TagContents = TSQLDocumentor.CommentExtractor.ExtractTagContents(ObjectDefinition, dvm.IncludeCodeTags);
                            if (TagContents.Length > 0)
                            {


                                DocumentaionIndex += string.Format("\n* [{0}.{1} ({2})](#{0}{1}{2})", dr["SCHEMA_NAME"].ToString(), dr["OBJECT_NAME"].ToString(), dr["type_desc"].ToString()).Replace("_", @"\_");
                                Documentaion += "\n<hr class='ObjectHeader'>";
                                Documentaion += "\n" + string.Format("# <a id='{0}{1}{2}'>", dr["SCHEMA_NAME"].ToString(), dr["OBJECT_NAME"].ToString(), dr["type_desc"].ToString()) + string.Format("[{0}].[{1}] ({2})</a> #", dr["SCHEMA_NAME"].ToString(), dr["OBJECT_NAME"].ToString(), dr["type_desc"].ToString()).Replace("_", @"\_");
                                Documentaion += "\n<hr class='ObjectFooter'>";
                                Documentaion += TagContents;
                                if (dvm.IncludeReferencedObjects)
                                {
                                    foreach (string schema in SysObjects.Select(s => s.SchemaName).Distinct())
                                    {
                                        if (schema.ToLower() == "dbo" || ObjectDefinition.IndexOf(schema, 0, StringComparison.CurrentCultureIgnoreCase) > 0)
                                        {
                                            foreach (sysObjects sysObject in SysObjects.Where(s => s.SchemaName == schema))
                                            {
                                                if (!(sysObject.SchemaName == dr["SCHEMA_NAME"].ToString() && sysObject.ObjectName == dr["OBJECT_NAME"].ToString()))
                                                    if (ObjectDefinition.IndexOf(sysObject.ObjectName, 0, StringComparison.CurrentCultureIgnoreCase) > 0)
                                                    {
                                                        sysObjectsFound.Add(sysObject);
                                                    }
                                            }
                                        }
                                    }

                                    foreach (string typeName in sysObjectsFound.Select(t => t.TypeName).Distinct())
                                    {
                                        ReferencedObjects += "\n### " + typeName.Replace("_", " ");
                                        foreach (sysObjects foundSysObject in sysObjectsFound
                                                                                .Where(t => t.TypeName == typeName)
                                                                                .OrderBy(s => s.SchemaName)
                                                                                .ThenBy(o => o.ObjectName))
                                        {

                                            ReferencedObjects += string.Format("\n[{0}].[{1}]  ", foundSysObject.SchemaName.Replace("_", @"\_"), foundSysObject.ObjectName.Replace("_", @"\_"));
                                        }
                                    }

                                    Documentaion += "## Referenced Objects\n- - -" + ReferencedObjects;
                                }
                            }
                        }
                    }
                    cn.Close();
                }
            }
            anrControls.Markdown md = new anrControls.Markdown();
            dvm.HtmlDocumentationGuide = new HtmlString(md.Transform(DocumentaionGuide.Replace("  ", "")));
            dvm.HtmlDocumentationIndex = new HtmlString(md.Transform(DocumentaionIndex));
            dvm.HtmlDocumentation = new HtmlString(md.Transform(Documentaion));
            return View(dvm);
        }

        public ActionResult Index()
        {
            DocumentationViewModel dvm = new DocumentationViewModel();
            anrControls.Markdown md = new anrControls.Markdown();
            dvm.HtmlDocumentationGuide = new HtmlString(md.Transform(DocumentaionGuide.Replace("  ", "")));
            return View(dvm);
        }

    }
}
